<?php
require_once 'mysqlconnect.php';
//获得已经存在的所有商品的种类
function get_Good_Types() {
	global $dbc;
	$types = array ();
	
	$query = "SELECT * FROM goods_type";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	while ( $row ) {
		$oneType = array ();
		$oneType [0] = $row [0];
		$oneType [1] = $row [1];
		$oneType [2] = $row [2];
		
		$types [] = $oneType;
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	}
	return $types; //返回二维数组，上层数组每一项为数组{typeid,fathertypeid,typename}
}

//搜索满足要求的商品,gid,gname,gprice,gimage
function search_Good($type_id, $lowestPrice, $highestPrice) {
	global $dbc;
	$goods = array ();
	$type_id = escape_data ( $type_id );
	$lowestPrice = escape_data ( $lowestPrice );
	$highestPrice = escape_data ( $highestPrice );
	
	$query = "SELECT * FROM goods_moreinfo WHERE gtypeid='" . $type_id . "'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	while ( $row ) {
		$onegoods = array ();
		$gid = $row [0];
		$query1 = "SELECT * FROM goods_baseinfo WHERE gid='" . $gid . "'";
		$result1 = mysqli_query ( $dbc, $query1 );
		$row1 = mysqli_fetch_array ( $result1, MYSQL_NUM );
		
		if ($row1) {
			$onegoods [] = $row1 [0];
			$onegoods [] = $row1 [1];
			$onegoods [] = $row1 [2];
		}else{
			$onegoods [] = "";
			$onegoods [] = "";
			$onegoods [] = "";
		}
		if ($lowestPrice != "" && $highestPrice != "") {
			if ($row1 [2] > $lowestPrice && $row1 [2] < $highestPrice) {
				$query2 = "SELECT * FROM goods_image WHERE goodsid='" . $gid . "' and imagetype='0'";
				$result2 = mysqli_query ( $dbc, $query2 );
				$row2 = mysqli_fetch_array ( $result2, MYSQL_NUM );
				if ($row2) {
					$onegoods [] = $row2 [1]; //imgetpath
				} else {
					$onegoods [] = ""; //imgetpath
				}
				$goods [] = $onegoods;
			}
		} else {
			$query2 = "SELECT * FROM goods_image WHERE goodsid='" . $row1 [0] . "' and imagetype='0'";
			$result2 = mysqli_query ( $dbc, $query2 );
			$row2 = mysqli_fetch_array ( $result2, MYSQL_NUM );
			if ($row2) {
				$onegoods [] = $row2 [1]; //imgetpath
			} else {
				$onegoods [] = ""; //imgetpath
			}
			$goods [] = $onegoods;
		}
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	}
	
	return $goods;
}

//获得最新的12个商品
function get_New_Goods() {
	global $dbc;
	$newgoods = array ();
	
	$query = "SELECT * FROM goods_moreinfo order by gtime";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	$i = 0;
	while ( $row && $i < 12 ) {
		$onegoods = array ();
		$onegoods [] = $row [0]; //gid
		

		$query1 = "SELECT * FROM goods_baseinfo WHERE gid = '" . $row [0] . "'";
		$result1 = mysqli_query ( $dbc, $query1 );
		$row1 = mysqli_fetch_array ( $result1, MYSQL_NUM );
		if ($row1) {
			$onegoods [] = $row1 [1]; //gname
			$onegoods [] = $row1 [2];
			$onegoods [] = $row1 [3];
			$onegoods [] = $row1 [4];
			$onegoods [] = $row1 [5];
			$onegoods [] = $row1 [6]; //gleftcount
		} else {
			$onegoods [] = ""; //gname
			$onegoods [] = "";
			$onegoods [] = "";
			$onegoods [] = "";
			$onegoods [] = "";
			$onegoods [] = ""; //gleftcount
		}
		$onegoods [] = $row [1]; //illustrate
		$onegoods [] = $row [2];
		$onegoods [] = $row [3]; //typeid
		

		$query2 = "SELECT * FROM goods_image WHERE goodsid='" . $row [0] . "' and imagetype='0'";
		$result2 = mysqli_query ( $dbc, $query2 );
		$row2 = mysqli_fetch_array ( $result2, MYSQL_NUM );
		if ($row2) {
			$onegoods [] = $row2 [1]; //imgetpath
		} else {
			$onegoods [] = ""; //imgetpath
		}
		
		$query3 = "SELECT * FROM goods_type WHERE gtypeid='" . $onegoods [9] . "'";
		$result3 = mysqli_query ( $dbc, $query );
		$row3 = mysqli_fetch_array ( $result3, MYSQL_NUM );
		if ($row3) {
			$onegoods [] = $row3 [2]; //typename
		} else {
			$onegoods [] = "";
		}
		
		$newgoods [] = $onegoods;
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
		$i ++;
	}
	return $newgoods;
}

//获得最热门的12个商品
function get_Popular_Goods() {
	global $dbc;
	$populate = array ();
	
	$query = "SELECT * FROM goods_baseinfo order by gsoldcount";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	$i = 0;
	while ( $row && $i < 12 ) {
		$onegoods = array ();
		$onegoods [] = $row [0]; //gid
		$onegoods [] = $row [1]; //gname
		$onegoods [] = $row [2];
		$onegoods [] = $row [3];
		$onegoods [] = $row [4];
		$onegoods [] = $row [5];
		$onegoods [] = $row [6]; //gleftcount
		

		$query1 = "SELECT * FROM goods_moreinfo WHERE gid = '" . $row [0] . "'";
		$result1 = mysqli_query ( $dbc, $query1 );
		$row1 = mysqli_fetch_array ( $result1, MYSQL_NUM );
		if ($row1) {
			$onegoods [] = $row1 [1]; //illustrate
			$onegoods [] = $row1 [2];
			$onegoods [] = $row1 [3]; //typeid
		} else {
			$onegoods [] = ""; //illustrate
			$onegoods [] = "";
			$onegoods [] = ""; //typeid
		}
		
		$query2 = "SELECT * FROM goods_image WHERE goodsid='" . $row [0] . "' and imagetype='0'";
		$result2 = mysqli_query ( $dbc, $query2 );
		$row2 = mysqli_fetch_array ( $result2, MYSQL_NUM );
		if ($row2) {
			$onegoods [] = $row2 [1]; //imgetpath
		} else {
			$onegoods [] = "";
		}
		
		$query3 = "SELECT * FROM goods_type WHERE gtypeid='" . $onegoods [9] . "'";
		$result3 = mysqli_query ( $dbc, $query );
		$row3 = mysqli_fetch_array ( $result3, MYSQL_NUM );
		if ($row3) {
			$onegoods [] = $row3 [2]; //typename
		} else {
			$onegoods [] = "";
		}
		
		$populate [] = $onegoods;
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
		$i ++;
	}
	return $populate;
}

//通过传入的id获得商品的详细信息 顺序是id，名称，价格, 图片
function get_Good($id) {
	global $dbc;
	$goodInfo = array ();
	$goodsID = escape_data ( $id );
	
	$query = "SELECT * FROM goods_baseinfo WHERE gid='" . $goodsID . "'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	if ($row) {
		$goodInfo [] = $row [0];
		$goodInfo [] = $row [1];
		$goodInfo [] = $row [2];
	}
	
	$query = "SELECT * FROM goods_image WHERE goodsid='" . $goodsID . "' and imagetype='0'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	if ($row) {
		$goodInfo [] = $row [1];
	}
	
	return $goodInfo;
}

//通过传入的id获得商品的大图
function get_Big_Picture($id) {
	global $dbc;
	$goodInfo = array ();
	$goodsID = escape_data ( $id );
	
	$query = "SELECT * FROM goods_image WHERE goodsid='" . $goodsID . "' and imagetype='1'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	while ( $row ) {
		$goodInfo [] = $row [1];
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	}
	
	return $goodInfo;
}

//通过username获得用户所有的详细信息,返回顺序为用户名,性别,等级,得分
function get_Base_Info($username) {
	global $dbc;
	$username = escape_data ( $username );
	$userInfo = array ();
	
	$query = "SELECT * FROM user_account WHERE userid='" . $username . "'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	if ($row) {
		$userInfo [] = $row [0];
		$userInfo [] = $row [3];
		$userInfo [] = $row [2];
		$userInfo [] = $row [4];
	}
	return $userInfo;
}

//通过用户名获得银行账户信息，返回数组
function get_Bank_Info($username) {
	global $dbc;
	$username = escape_data ( $username );
	$userCards = array ();
	
	$query = "SELECT * FROM user_card WHERE userid='" . $username . "'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	while ( $row ) {
		$userCard = $row [1];
		$userCards [] = $userCard;
		
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	}
	return $userCards;
}

//更改用户性别
function chang_gendar($username, $gendar) {
	global $dbc;
	$username = escape_data ( $username );
	$gendar = escape_data ( $gendar );
	$query = "UPDATE user_account SET gender=" . $gendar . " WHERE userid='" . $username . "'";
	$result = mysqli_query ( $dbc, $query );
	if ($result) {
		return TRUE;
	} else {
		return FALSE;
	}
}

//验证密码是否正确
function verify_password($username, $password) {
	global $dbc;
	$username = escape_data ( $username );
	$password = escape_data ( $password );
	
	$query = "SELECT * FROM user_account WHERE userid='" . $username . "'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	if ($row) {
		if ($row [1] == $password) {
			return TRUE;
		} else {
			return FALSE;
		}
	} else {
		return FALSE;
	}
}
//更改用户密码
function change_password($username, $password) {
	global $dbc;
	$username = escape_data ( $username );
	$password = escape_data ( $password );
	
	$query = "UPDATE user_account SET password='" . $password . "'WHERE userid='" . $username . "'";
	$result = mysqli_query ( $dbc, $query );
	if ($result) {
		return TRUE;
	} else {
		return FALSE;
	}
}

//更改用户帐号
function change_bank_account($username, $bank_accounts) {
	global $dbc;
	$username = escape_data ( $username );
	
	$query = "DELETE * FROM user_card WHERE userid='" . $username . "'";
	$result = mysqli_query ( $dbc, $query );
	foreach ( $bank_accounts as $onecard ) {
		$onecard = escape_data ( $onecard );
		$query = "INSERT INTO user_card (userid, cardnum) VALUES ('" . $username . "', '" . $onecard . "')";
		$result = mysqli_query ( $dbc, $query );
	}
	return TRUE;
}

function log_in($n, $p) {
	$n = escape_data ( $n );
	$p = escape_data ( $p );
	
	global $dbc;
	$query = "SELECT * FROM user_account WHERE userid='" . $n . "'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	if ($row) {
		if ($row [1] == $p) {
			return TRUE;
		} else {
			return FALSE;
		}
	} else {
		return FALSE;
	}
}

function register($username, $password) { //注册成功返回TRUE，失败返回FALSE
	global $dbc;
	
	$username = escape_data ( $username );
	$password = escape_data ( $password );
	
	$query = "INSERT INTO user_account (userid, password) VALUES ('" . $username . "','" . $password . "')";
	$result = mysqli_query ( $dbc, $query );
	if ($result) {
		return TRUE;
	} else {
		return FALSE;
	}
}

//传入的参数为商品id，以数组形式返回前10个评论内容（只有内容）
function get_comments($id) {
	global $dbc;
	$goodsid = escape_data($id);
	$comments = array();
	
	$query = "SELECT * FROM goods_comment WHERE goodsid = '".$goodsid."'";
	$result = mysqli_query ( $dbc, $query );
	$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	while($row){
		$comments[] = $row[2];
		
		$row = mysqli_fetch_array ( $result, MYSQL_NUM );
	}
	return $comments;
}

//添加对商品的评价，第一个参数为用户名，第二个参数为商品的id，第三个参数为评价的内容,第四个参数是给商品评的分
function add_comment($username, $goods_id, $comment, $grade) {
	global $dbc;
	$username = escape_data($username);
	$goods_id = escape_data($goods_id);
	$comment = escape_data($comment);
	$grade = escape_data($grade);
	
	$time = date ( "Y-m-d H:i:s" );
	$query = "INSERT INTO goods_comment (userid, content, goodsid, ctime) VALUES ('" . $username . "','" . $comment . "','".$goods_id."','".$time."')";
	$result = mysqli_query ( $dbc, $query );
	
	$query1 = "SELECT * FROM goods_baseinfo WHERE gid='" . $goods_id . "'";
	$result1 = mysqli_query ( $dbc, $query1 );
	$row1 = mysqli_fetch_array ( $result1, MYSQL_NUM );
	if ($row1) {
		$formal_score = $row1[3];
		$comment_num = $row1[5];
		$new_grade = ($formal_score*$comment_num+$grade)/($comment_num+1);
		echo $new_grade;
		
		$query3 = "UPDATE goods_baseinfo SET gscore='" . $new_grade . "', gcommentcount=gcommentcount+1 WHERE gid='" . $goods_id . "'";
		$result3 = mysqli_query ( $dbc, $query3 );
	}
	
	if($result && $result3){
		return TRUE;
	}else{
		return FALSE;
	}
}

?>